package org.springboot.szsrcb.srvice;

import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springboot.szsrcb.entity.Attachment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

@Service
public class Att_Service {
	private static final Logger logger = LoggerFactory.getLogger(Att_Service.class);

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public Attachment new_req_add_a_att(Map<String,Object> map) {
		Integer get_r;
		get_r=-1;
		Attachment r_att=null;
		//Map<String,Object> r_map=new HashMap<String,Object>();
		MultipartFile mf=(MultipartFile) map.get("MultipartFile");
		InputStream inputStream=(InputStream) map.get("InputStream");
		int v_relation_req_id=(int) map.get("relation_req_id");
			
		map.put("relation_req_id", v_relation_req_id);
		String sql = "INSERT INTO attachments 	(att_file_name,add_datetime,att_data,relation_req_id,att_status,att_size) VALUES (\'"+ mf.getOriginalFilename()+"\',NOW(),?,?,'接收需求',?);";
		logger.info("从数据库执行" + sql);

		try {			
	
			LobHandler lobhandler=new DefaultLobHandler();
		
			get_r=jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(lobhandler){
				@Override
				protected void setValues(java.sql.PreparedStatement ps, LobCreator lobCreator)
						throws SQLException, DataAccessException {
					lobCreator.setBlobAsBinaryStream(ps, 1, inputStream, (int) mf.getSize());
					ps.setInt(2, v_relation_req_id);
					ps.setInt(3, (int) mf.getSize());
				}
				
			});
			logger.info("get_r :" + get_r);
			//query att id
			if(get_r==1){
			String sql2 = "Select * FROM attachments WHERE att_file_name = \'"+ mf.getOriginalFilename()+"\' AND relation_req_id = "+v_relation_req_id+" ;";
			logger.info("sql2 :" + sql2);
			List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
			r_att=l_att.get(0);
			}
		} catch (Exception e) {
			logger.info("DB Exception :" +e.getMessage());
			
		}
		return r_att;
		
	}
	
	public Attachment modify_req_add_a_att(Map<String,Object> map) {
		Integer get_r;
		get_r=-1;
		Attachment r_att=null;
		//Map<String,Object> r_map=new HashMap<String,Object>();
		MultipartFile mf=(MultipartFile) map.get("MultipartFile");
		InputStream inputStream=(InputStream) map.get("InputStream");
		int v_relation_req_id=(int) map.get("relation_req_id");
			
		map.put("relation_req_id", v_relation_req_id);
		String sql = "INSERT INTO attachments 	(att_file_name,add_datetime,att_data,relation_req_id,att_status,att_size) VALUES (\'"+ mf.getOriginalFilename()+"\',NOW(),?,?,'修改需求',?);";
		logger.info("从数据库执行" + sql);

		try {			
	
			LobHandler lobhandler=new DefaultLobHandler();
		
			get_r=jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(lobhandler){
				@Override
				protected void setValues(java.sql.PreparedStatement ps, LobCreator lobCreator)
						throws SQLException, DataAccessException {
					lobCreator.setBlobAsBinaryStream(ps, 1, inputStream, (int) mf.getSize());
					ps.setInt(2, v_relation_req_id);
					ps.setInt(3, (int) mf.getSize());
				}
				
			});
			logger.info("get_r :" + get_r);
			//query att id
			if(get_r==1){
			String sql2 = "Select * FROM attachments WHERE att_file_name = \'"+ mf.getOriginalFilename()+"\' AND relation_req_id = "+v_relation_req_id+" ;";
			logger.info("sql2 :" + sql2);
			List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
			r_att=l_att.get(0);
			}
		} catch (Exception e) {
			logger.info("DB Exception :" +e.getMessage());
			
		}
		return r_att;
		
	}
	
	public Attachment new_test_add_a_att(Map<String,Object> map) {
		Integer get_r;
		get_r=-1;
		Attachment r_att=null;
		//Map<String,Object> r_map=new HashMap<String,Object>();
		MultipartFile mf=(MultipartFile) map.get("MultipartFile");
		InputStream inputStream=(InputStream) map.get("InputStream");
		int v_relation_test_id=(int) map.get("relation_test_id");
			
		map.put("relation_req_id", v_relation_test_id);
		String sql = "INSERT INTO attachments 	(att_file_name,add_datetime,att_data,relation_test_id,att_status,att_size) VALUES (\'"+ mf.getOriginalFilename()+"\',NOW(),?,?,'接收测试',?);";
		logger.info("从数据库执行" + sql);

		try {			
	
			LobHandler lobhandler=new DefaultLobHandler();
		
			get_r=jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(lobhandler){
				@Override
				protected void setValues(java.sql.PreparedStatement ps, LobCreator lobCreator)
						throws SQLException, DataAccessException {
					lobCreator.setBlobAsBinaryStream(ps, 1, inputStream, (int) mf.getSize());
					ps.setInt(2, v_relation_test_id);
					ps.setInt(3, (int) mf.getSize());
				}
				
			});
			logger.info("get_r :" + get_r);
			//query att id
			if(get_r==1){
			String sql2 = "Select * FROM attachments WHERE att_file_name = \'"+ mf.getOriginalFilename()+"\' AND relation_test_id = "+v_relation_test_id+" ;";
			logger.info("sql2 :" + sql2);
			List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
			r_att=l_att.get(0);
			}
		} catch (Exception e) {
			logger.info("DB Exception :" +e.getMessage());
			
		}
		return r_att;
		
	}
	
	public Attachment modify_test_add_a_att(Map<String,Object> map) {
		Integer get_r;
		get_r=-1;
		Attachment r_att=null;
		//Map<String,Object> r_map=new HashMap<String,Object>();
		MultipartFile mf=(MultipartFile) map.get("MultipartFile");
		InputStream inputStream=(InputStream) map.get("InputStream");
		int v_relation_test_id=(int) map.get("relation_test_id");
			
		map.put("relation_req_id", v_relation_test_id);
		String sql = "INSERT INTO attachments 	(att_file_name,add_datetime,att_data,relation_test_id,att_status,att_size) VALUES (\'"+ mf.getOriginalFilename()+"\',NOW(),?,?,'修改测试',?);";
		logger.info("从数据库执行" + sql);

		try {			
	
			LobHandler lobhandler=new DefaultLobHandler();
		
			get_r=jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(lobhandler){
				@Override
				protected void setValues(java.sql.PreparedStatement ps, LobCreator lobCreator)
						throws SQLException, DataAccessException {
					lobCreator.setBlobAsBinaryStream(ps, 1, inputStream, (int) mf.getSize());
					ps.setInt(2, v_relation_test_id);
					ps.setInt(3, (int) mf.getSize());
				}
				
			});
			logger.info("get_r :" + get_r);
			//query att id
			if(get_r==1){
			String sql2 = "Select * FROM attachments WHERE att_file_name = \'"+ mf.getOriginalFilename()+"\' AND relation_test_id = "+v_relation_test_id+" ;";
			logger.info("sql2 :" + sql2);
			List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
			r_att=l_att.get(0);
			}
		} catch (Exception e) {
			logger.info("DB Exception :" +e.getMessage());
			
		}
		return r_att;
		
	}
	
	public Attachment assign_test_add_a_att(Map<String,Object> map) {
		Integer get_r;
		get_r=-1;
		Attachment r_att=null;
		//Map<String,Object> r_map=new HashMap<String,Object>();
		MultipartFile mf=(MultipartFile) map.get("MultipartFile");
		InputStream inputStream=(InputStream) map.get("InputStream");
		int v_relation_test_id=(int) map.get("relation_test_id");
			
		//map.put("relation_req_id", v_relation_test_id);
		String sql = "INSERT INTO attachments 	(att_file_name,add_datetime,att_data,relation_test_id,att_status,att_size) VALUES (\'"+ mf.getOriginalFilename()+"\',NOW(),?,?,'分配测试',?);";
		logger.info("从数据库执行" + sql);

		try {			
	
			LobHandler lobhandler=new DefaultLobHandler();
		
			get_r=jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(lobhandler){
				@Override
				protected void setValues(java.sql.PreparedStatement ps, LobCreator lobCreator)
						throws SQLException, DataAccessException {
					lobCreator.setBlobAsBinaryStream(ps, 1, inputStream, (int) mf.getSize());
					ps.setInt(2, v_relation_test_id);
					ps.setInt(3, (int) mf.getSize());
				}
				
			});
			logger.info("get_r :" + get_r);
			//query att id
			if(get_r==1){
			String sql2 = "Select * FROM attachments WHERE att_file_name = \'"+ mf.getOriginalFilename()+"\' AND relation_test_id = "+v_relation_test_id+" ;";
			logger.info("sql2 :" + sql2);
			List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
			r_att=l_att.get(0);
			}
		} catch (Exception e) {
			logger.info("DB Exception :" +e.getMessage());
			
		}
		return r_att;
		
	}
	
	class AttRowMapper implements RowMapper<Attachment>{
		 @Override
        public Attachment mapRow(ResultSet rs, int rowNum) throws SQLException {
			 Attachment att = new Attachment();
			 att.setId(rs.getInt("ID"));
			 att.setAdd_datetime(rs.getDate("add_datetime"));
			 att.setAtt_status(rs.getString("att_status"));
			 att.setAtt_file_name(rs.getString("att_file_name"));
			 att.setRelation_req_id(rs.getString("relation_req_id"));
			 att.setAtt_size(rs.getInt("att_size"));
			 att.setAtt_data(rs.getBlob("att_data"));
            /*u.setId(rs.getInt("ID"));	               
            u.setLogin_name(rs.getString("LOGIN_NAME"));
            u.setPwd("PWD");*/
            return att;
        }

	}
	
	public Boolean req_new_update_att(Map<String,Object> map) {
		String relation_req_id=(String) map.get("relation_req_id");
		String att_ids=(String) map.get("att_ids");
		String sql_u = "Update  attachments SET relation_req_id = '"+ relation_req_id+"' , att_status='接收需求' WHERE ID IN ("+att_ids+");";
		logger.info("sql_u :" + sql_u);
		 int get_r=jdbcTemplate.update(sql_u);
		if (get_r==1){
			return true;
		}else{
			return false;
		}
		
		
	}

	public Boolean req_modify_update_att(Map<String,Object> map) {
		String relation_req_id=(String) map.get("relation_req_id");
		String att_ids=(String) map.get("att_ids");
		String sql_u = "Update  attachments SET relation_req_id = '"+ relation_req_id+"' , att_status='修改需求' WHERE ID IN ("+att_ids+");";
		logger.info("sql_u :" + sql_u);
		 int get_r=jdbcTemplate.update(sql_u);
		if (get_r==1){
			return true;
		}else{
			return false;
		}		
	}
	
	public Boolean req_update_att(Map<String,Object> map) {
		String relation_req_id=(String) map.get("relation_req_id");
		String att_ids=(String) map.get("att_ids");
		String sql_u = "Update  attachments SET relation_req_id = '"+ relation_req_id+"' , att_status='分配需求' WHERE ID IN ("+att_ids+");";
		logger.info("sql_u :" + sql_u);
		 int get_r=jdbcTemplate.update(sql_u);
		if (get_r==1){
			return true;
		}else{
			return false;
		}		
	}

	public Boolean test_update_att(Map<String, Object> map) {
		int relation_test_id=(int) map.get("relation_test_id");
		String att_ids=(String) map.get("att_ids");
		String sql_u = "Update  attachments SET relation_test_id = '"+ relation_test_id+"' , att_status='分配测试' WHERE ID IN ("+att_ids+");";
		logger.info("sql_u :" + sql_u);
		 int get_r=jdbcTemplate.update(sql_u);
		if (get_r>=1){
			return true;
		}else{
			return false;
		}
		
	}
	
	public Boolean new_test_update_att(Map<String, Object> map) {
		String relation_test_id=(String) map.get("relation_test_id");
		String att_ids=(String) map.get("att_ids");
		String sql_u = "Update  attachments SET relation_test_id = '"+ relation_test_id+"' , att_status='接收测试' WHERE ID IN ("+att_ids+");";
		logger.info("sql_u :" + sql_u);
		 int get_r=jdbcTemplate.update(sql_u);
		if (get_r>0){
			return true;
		}else{
			return false;
		}
		
	}

	public List<Attachment> search_old_atts_by_req_id(Integer integer) {
		String sql2 = "Select * FROM attachments WHERE relation_req_id = "+integer+" ;";
		logger.info("sql2 :" + sql2);
		List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
		if (l_att==null) {
			return null;
		}else{
			return l_att;
		}
		
	}
	
	
	public Attachment search_a_att_by_id(String get_att_id) {
		String sql2 = "Select * FROM attachments WHERE id = "+get_att_id+" ;";
		logger.info("sql2 :" + sql2);
		List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
		if (l_att==null) {
			return null;
		}else{
			return l_att.get(0);
		}
	}

	public boolean del_a_att_by_id(String get_att_id) {
		String sql2 = "DELETE FROM attachments WHERE id="+get_att_id;
		logger.info("sql2 :" + sql2);
		int r= jdbcTemplate.update(sql2);
		if (r>0) {
			return true;
		}else{
			return false;
		}	
	}

	public List<Attachment> search_old_atts_by_test_id(String g_test_id) {
		String sql2 = "Select * FROM attachments WHERE relation_test_id = "+g_test_id+" ;";
		logger.info("sql2 :" + sql2);
		List<Attachment> l_att= jdbcTemplate.query(sql2,new AttRowMapper());
		if (l_att==null) {
			return null;
		}else{
			return l_att;
		}
	}
	
	/*public static void download(HttpServletRequest request,  
            HttpServletResponse response, String storeName, String contentType,  
            String realName) throws Exception {  
        response.setContentType("text/html;charset=UTF-8");  
        request.setCharacterEncoding("UTF-8");  
        BufferedInputStream bis = null;  
        BufferedOutputStream bos = null;  
  
        String ctxPath = request.getSession().getServletContext()  
                .getRealPath("/")  
                + FileOperateUtil.UPLOADDIR;  
       // String downLoadPath = ctxPath + storeName;  
  
        long fileLength = new File(downLoadPath).length();  
  
        response.setContentType(contentType);  
        response.setHeader("Content-disposition", "attachment; filename="  
                + new String(realName.getBytes("utf-8"), "ISO8859-1"));  
        response.setHeader("Content-Length", String.valueOf(fileLength));  
  
        bis = new BufferedInputStream(new FileInputStream(downLoadPath));  
        bos = new BufferedOutputStream(response.getOutputStream());  
        byte[] buff = new byte[2048];  
        int bytesRead;  
        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {  
            bos.write(buff, 0, bytesRead);  
        }  
        bis.close();  
        bos.close();  
    }  
*/
	
	
}
